Working with functions

Insert function

The ability to perform basic calculations is the principal reason for using a spreadsheet. Some of them are performed automatically when you select a cell range in your spreadsheet:

• Average is used to analyze the selected cell range and find the average value.

• Count is used to count the number of the selected cells with values ignoring the empty cells.

• Min is used to analyze the range of data and find the smallest number.

• Max is used to analyze the range of data and find the largest number.

• Sum is used to add all the numbers in the selected range ignoring the empty cells or those contaning text.

The results of these calculations are displayed in the right lower corner on the status bar. You can manage the status bar by right-clicking on it and choosing only those functions to display that you need.

To perform any other calculations, you can insert the required formula manually using the common mathematical operators or insert a predefined formula - Function.

The abilities to work with Functions are accessible from both the Home and Formula tab or by pressing Shift+F3 key combination. On the Home tab, you can use the Insert function button to add one of the most commonly used functions (SUM, AVERAGE, MIN, MAX, COUNT) or open the Insert Function window that contains all the available functions classified by category. Use the search box to find the exact function by its name.

On the Formula tab you can use the following buttons:

• Function: to open theInsert Functionwindow that contains all the available functions classified by category.

• Autosum: to quickly access the SUM, MIN, MAX, COUNT functions. When you select a functions from this group, it automatically performs calculations for all cells in the column above the selected cell so that you don't need to enter arguments.

• Recently used: to quickly access 10 recently used functions.

• Financial, Logical, Text and data, Date and time, Lookup and references, Math and trigonometry: to quickly access functions that belongs to the corresponding categories.

• More functions: to access the functions from the following groups:Database,Engineering,Informationand Statistical.

• Named ranges: to open the Name Manager, or define a new name, or paste a name as a function argument.

• Calculation: to force the program to recalculate functions.

To insert a function,

1. Select a cell where you wish to insert a function.

2. Proceed in one of the following ways:

• switch to theFormulatab and use the buttons available on the top toolbar to access a function from a specific group, then click the necessary function to open theFunction Argumentswizard. You can also use theAdditionaloption from the menu or click the Functionbutton on the top toolbar to open theInsert Functionwindow.

• switch to the Home tab , click theInsert function icon, select one of the commonly used functions (SUM, AVERAGE, MIN, MAX, COUNT) or click theAdditionaloption to open theInsert Functionwindow.

• right-click within the selected cell and select theInsert Functionoption from the contextual menu.

• click the  icon before the formula bar.

3. In the openedInsert Functionwindow, enter its name in the search box or select the necessary function group, then choose the required function from the list and click OK.

   Once you click the necessary function, the Function Arguments window will open:

   

4. In the openedFunction Argumentswindow, enter the necessary values of each argument.

   You can enter the function arguments either manually or by clicking the icon and selecting a cell or cell range to be included as an argument.

   Note: Generally, numeric values, logical values (TRUE, FALSE), text values (must be quoted), cell references, cell range references, names assigned to ranges and other functions can be used as function arguments.

   The function result will be displayed below

5. When all the arguments are specified, click the OK button in theFunction Argumentswindow.

To enter a function manually using the keyboard,

1. Select a cell.

2. Enter the equal sign (=).

   Each formula must begin with the equal sign (=).

3. Enter the function name.

   Once you type the initial letters, theFormula Autocompletelist will be displayed. As you type, the items (formulas and names) that match the entered characters are displayed in it. If you hover the mouse pointer over a formula, a tooltip with the formula description will be displayed. You can select the necessary formula from the list and insert it by clicking it or pressing theTabkey.

4. Enter the function arguments either manually or by dragging to select a cell range to be included as an argument. If the function requires several arguments, they must be separated by commas.

   Arguments must be enclosed into parentheses. The opening parenthesis '(' is added automatically if you select a function from the list. When you enter arguments, a tooltip that contains the formula syntax is also displayed.

5. When all the arguments are specified, enter the closing parenthesis ')' and press Enter.

If you enter new data or change the values used as arguments, recalculation of functions is performed automatically by default. You can force the program to recalculate functions by using the Calculation button on the Formula tab. Click the Calculation button to recalculate the entire workbook, or click the arrow below the button and choose the necessary option from the menu: Calculate workbook or Calculate current sheet.

You can also use the following key combinations: F9 to recalculate the workbook, Shift +F9 to recalculate the current worksheet.

Here is the list of the available functions grouped by categories:

Function Category	Description	Functions
Text and Data Functions	Used to correctly display the text data in the spreadsheet.	ASC; CHAR; CLEAN; CODE; CONCATENATE; CONCAT; DOLLAR; EXACT; FIND; FINDB; FIXED; LEFT; LEFTB; LEN; LENB; LOWER; MID; MIDB; NUMBERVALUE; PROPER; REPLACE; REPLACEB; REPT; RIGHT; RIGHTB; SEARCH; SEARCHB; SUBSTITUTE; T; TEXT; TEXTJOIN; TRIM; UNICHAR; UNICODE; UPPER; VALUE
Statistical Functions	Used to analyze data: finding the average value, the largest or smallest values in a cell range.	AVEDEV; AVERAGE; AVERAGEA; AVERAGEIF; AVERAGEIFS; BETADIST; BETA.DIST; BETA.INV; BETAINV; BINOMDIST; BINOM.DIST; BINOM.DIST.RANGE; BINOM.INV; CHIDIST; CHIINV; CHISQ.DIST; CHISQ.DIST.RT; CHISQ.INV; CHISQ.INV.RT; CHITEST; CHISQ.TEST; CONFIDENCE; CONFIDENCE.NORM; CONFIDENCE.T; CORREL; COUNT; COUNTA; COUNBLANK; COUNTIF; COUNTIFS; COVAR; COVARIANCE.P; COVARIANCE.S; CRITBINOM; DEVSQ; EXPON.DIST; EXPONDIST; F.DIST; FDIST; F.DIST.RT; F.INV; FINV; F.INV.RT; FISHER; FISHERINV; FORECAST; FORECAST.ETS; FORECAST.ETS.CONFINT; FORECAST.ETS.SEASONALITY; FORECAST.ETS.STAT; FORECAST.LINEAR; FREQUENCY; FTEST; F.TEST; GAMMA; GAMMA.DIST; GAMMADIST; GAMMA.INV; GAMMAINV; GAMMALN; GAMMALN.PRECISE; GAUSS; GEOMEAN; GROWTH; HARMEAN; HYPGEOMDIST; HYPGEOM.DIST; INTERCEPT; KURT; LARGE; LINEST; LOGEST, LOGINV; LOGNORM.DIST; LOGNORM.INV; LOGNORMDIST; MAX; MAXA; MAXIFS; MEDIAN; MIN; MINA; MINIFS; MODE; MODE.MULT; MODE.SNGL; NEGBINOMDIST; NEGBINOM.DIST; NORMDIST; NORM.DIST; NORMINV; NORM.INV; NORMSDIST; NORM.S.DIST; NORMSINV; NORM.S.INV; PEARSON; PERCENTILE; PERCENTILE.EXC; PERCENTILE.INC; PERCENTRANK; PERCENTRANK.EXC; PERCENTRANK.INC; PERMUT; PERMUTATIONA; PHI; POISSON; POISSON.DIST; PROB; QUARTILE; QUARTILE.EXC; QUARTILE.INC; RANK; RANK.AVG; RANK.EQ; RSQ; SKEW; SKEW.P; SLOPE; SMALL; STANDARDIZE; STDEV; STDEV.S; STDEVA; STDEVP; STDEV.P; STDEVPA; STEYX; TDIST; T.DIST; T.DIST.2T; T.DIST.RT; T.INV; T.INV.2T; TINV; TREND, TRIMMEAN; TTEST; T.TEST; VAR; VARA; VARP; VAR.P; VAR.S; VARPA; WEIBULL; WEIBULL.DIST; ZTEST; Z.TEST
Math and Trigonometry Functions	Used to perform basic math and trigonometry operations such as adding, multiplying, dividing, rounding, etc.	ABS; ACOS; ACOSH; ACOT; ACOTH; AGGREGATE; ARABIC; ASIN; ASINH; ATAN; ATAN2; ATANH; BASE; CEILING; CEILING.MATH; CEILING.PRECISE; COMBIN; COMBINA; COS; COSH; COT; COTH; CSC; CSCH; DECIMAL; DEGREES; ECMA.CEILING; EVEN; EXP; FACT; FACTDOUBLE; FLOOR; FLOOR.PRECISE; FLOOR.MATH; GCD; INT; ISO.CEILING; LCM; LN; LOG; LOG10; MDETERM; MINVERSE; MMULT; MOD; MROUND; MULTINOMIAL; MUNIT; ODD; PI; POWER; PRODUCT; QUOTIENT; RADIANS; RAND; RANDARRAY; RANDBETWEEN; ROMAN; ROUND; ROUNDDOWN; ROUNDUP; SEC; SECH; SERIESSUM; SIGN; SIN; SINH; SQRT; SQRTPI; SUBTOTAL; SUM; SUMIF; SUMIFS; SUMPRODUCT; SUMSQ; SUMX2MY2; SUMX2PY2; SUMXMY2; TAN; TANH; TRUNC
Date and Time Functions	Used to correctly display the date and time in the spreadsheet.	DATE; DATEDIF; DATEVALUE; DAY; DAYS; DAYS360; EDATE; EOMONTH; HOUR; ISOWEEKNUM; MINUTE; MONTH; NETWORKDAYS; NETWORKDAYS.INTL; NOW; SECOND; TIME; TIMEVALUE; TODAY; WEEKDAY; WEEKNUM; WORKDAY; WORKDAY.INTL; YEAR; YEARFRAC
Engineering Functions	Used to perform some engineering calculations: converting between different bases number systems, finding complex numbers etc.	BESSELI; BESSELJ; BESSELK; BESSELY; BIN2DEC; BIN2HEX; BIN2OCT; BITAND; BITLSHIFT; BITOR; BITRSHIFT; BITXOR; COMPLEX; CONVERT; DEC2BIN; DEC2HEX; DEC2OCT; DELTA; ERF; ERF.PRECISE; ERFC; ERFC.PRECISE; GESTEP; HEX2BIN; HEX2DEC; HEX2OCT; IMABS; IMAGINARY; IMARGUMENT; IMCONJUGATE; IMCOS; IMCOSH; IMCOT; IMCSC; IMCSCH; IMDIV; IMEXP; IMLN; IMLOG10; IMLOG2; IMPOWER; IMPRODUCT; IMREAL; IMSEC; IMSECH; IMSIN; IMSINH; IMSQRT; IMSUB; IMSUM; IMTAN; OCT2BIN; OCT2DEC; OCT2HEX
Database Functions	Used to perform calculations for the values in a certain field of the database that meet the specified criteria.	DAVERAGE; DCOUNT; DCOUNTA; DGET; DMAX; DMIN; DPRODUCT; DSTDEV; DSTDEVP; DSUM; DVAR; DVARP
Financial Functions	Used to perform some financial calculations: calculating the net present value, payments etc.	ACCRINT; ACCRINTM; AMORDEGRC; AMORLINC; COUPDAYBS; COUPDAYS; COUPDAYSNC; COUPNCD; COUPNUM; COUPPCD; CUMIPMT; CUMPRINC; DB; DDB; DISC; DOLLARDE; DOLLARFR; DURATION; EFFECT; FV; FVSCHEDULE; INTRATE; IPMT; IRR; ISPMT; MDURATION; MIRR; NOMINAL; NPER; NPV; ODDFPRICE; ODDFYIELD; ODDLPRICE; ODDLYIELD; PDURATION; PMT; PPMT; PRICE; PRICEDISC; PRICEMAT; PV; RATE; RECEIVED; RRI; SLN; SYD; TBILLEQ; TBILLPRICE; TBILLYIELD; VDB; XIRR; XNPV; YIELD; YIELDDISC; YIELDMAT
Lookup and Reference Functions	Used to easily find information from the data list.	ADDRESS; CHOOSE; COLUMN; COLUMNS; FORMULATEXT; HLOOKUP; HYPERLINLK; INDEX; INDIRECT; LOOKUP; MATCH; OFFSET; ROW; ROWS; TRANSPOSE; UNIQUE; VLOOKUP
Information Functions	Used to provide information about the data in the selected cell or cell range.	CELL; ERROR.TYPE; ISBLANK; ISERR; ISERROR; ISEVEN; ISFORMULA; ISLOGICAL; ISNA; ISNONTEXT; ISNUMBER; ISODD; ISREF; ISTEXT; N; NA; SHEET; SHEETS; TYPE
Logical Functions	Used to check if a condition is true or false.	AND; FALSE; IF; IFERROR; IFNA; IFS; NOT; OR; SWITCH; TRUE; XOR